Mindspace

Предисловие
...

Я всех приветствую!
Если вы вдруг рассматриваете себя на такую прекрасную роль в мире IT как Аналитик (любой, от Бизнес и Продуктового до Аналитика Данных), то для работы вам потребуется такой язык как SQL. существует много разных подвидов, называемых СУБД - они отличаются друг от друга не много чем, но некоторый функционал в одном месте убран, в другом добавлен, поэтому знать нужно всё.

Сегодня же мы быстренько разберём, что из себя представляет часть по SQL для отбора кандидатов на стажировку в такую крупную фирму как Tinkoff.
Перед началом только напомню, что потренироваться самостоятельно в знаниях SQL, в написании запросов на разных СУБД можно на сайте https://sql-ex.ru/
Ну а с вами, как всегда, Господин Сергей, специально для канала ТУЧ

SQL часть
...

№1
...


Inner Join используется именно для нахождения совпадающий значений в обоих таблицах

№2
...


Мало кто пользуется этой функцией, поэтому Интернет в помощь. Но Unique существует, и скорее всего ближе всего подходит чем другие ответы

№3
...


Согласно источнику,

  • Если в операторе DISTINCT указано только одно выражение, запрос возвратит уникальные значения для этого выражения.
  • Если в операторе DISTINCT указано несколько выражений, запрос извлекает уникальные комбинации для перечисленных выражений.
  • В SQL оператор DISTINCT не игнорирует значения NULL. Поэтому при использовании DISTINCT в вашем операторе SQL ваш результирующий набор будет содержать значение NULL в качестве отдельного значения.

№4
...

В интернете нашел за 20 секунд такую вот таблицу:


Согласно ней и получается нужный нам ответ

№5
...


В таком случае происходит написание всех возможных комбинаций из этих двух таблиц. Если не верите, создайте сами две таблицы, и действительно получите все сочетания, то есть len(a)*len(b)=100*

№6
...


Если вдруг у вас вызывает трудности эта задача, можете написать таблицу на сайте https://sqliteonline.com/ и самостоятельно проверить все варианты, и почему они не будут работать кроме того, что выделен уже на картинке

№7
...

  • Действительно, существует такой синтаксис написания для проверки четности чисел. Я на всякий случай реально создал таблицу и проверил, что что забыл, это нормально

№8
...


написание in(60000,80000) проверяет не диапазон (как было бы в случае between), а просто из списка значения

№9
...


Вернитесь к задаче 6 и посмотрите, Что только у id=2,3 нет Иван-а в названии. а написание Like и Not Like можно прочитать на https://sql-ex.ru/ , сам помню читал с удовольствием их теоретический материал.

№10
...


Подсмотрите в интернете, это гроб-вопрос.

№11
...


Как решать последнюю задачу

Мне нравится создавать временные таблицы на сайте https://sqliteonline.com/
Вот Примеры кодов и их запуски на сайте:

  1. Давайте создадим на сайте 2 Таблицы с примерными данными:
CREATE TABLE Staff
(
    staff_id INTEGER,
  	name str,
  	salary INTEGER,
    email str,
  	birthday str,
  	jobtitle_id INTEGER
);

CREATE TABLE Jobtitles
(
  	jobtitle_id INTEGER,
  	name str
);

INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (1, 'Сергей',100,'test@mail.ru','01.05.2001',3);
INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (2, 'Андрей',200,'gog@mail.ru','05.10.2004',4);
INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (3, 'Гоша',400,'test@mail.ru','10.05.2002',2);
INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (4, 'Вася',500,'tyu@mail.ru','21.02.2002',3);
INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (5, 'Иван',200,'tyu@mail.ru','01.08.2001',1);
INSERT INTO Staff (staff_id, name,salary,email,birthday,jobtitle_id) VALUES (6, 'Иван',150,'sdfh@mail.ru','01.05.2001',5);

INSERT INTO Jobtitles (jobtitle_id,name) VALUES (1,'Админ');
INSERT INTO Jobtitles (jobtitle_id,name) VALUES (2,'Прогер');
INSERT INTO Jobtitles (jobtitle_id,name) VALUES (3,'Уборщица');
INSERT INTO Jobtitles (jobtitle_id,name) VALUES (4,'Охранник');
INSERT INTO Jobtitles (jobtitle_id,name) VALUES (5,'Пекарь');
INSERT INTO Jobtitles (jobtitle_id,name) VALUES (6,'Управляющий');
  1. Проверим работоспособность этих таблиц, чтобы всё отображалось

  1. Всё запускается! Время написать все задачи
    Предлагаю сначала самостоятельно всё решить, а потом свериться со мной.

Решение
...

Я решил написать все запросы через ;, в нормальных редакторах они будут нормально отображаться как отдельные окна. Думаю, вам будем лучше, если вы самостоятельно изучите как работает каждая из функций, которую я использовал (но скажу по секрету, разобрав полностью, а главное без моих подсказок все эти 3 запроса, вы выучите 70% всего SQL)

SELECT * 
FROM Staff
WHERE email in (SELECT email
                FROM (select email, count(*) as doubles 
                      from Staff
                      GROUP BY email) AS t1
                WHERE doubles<>1)
ORDER by email,staff_id ASC ; 

SELECT staff_id,name, strftime('%Y', 'now') - strftime('%Y', birthdate) - (strftime('%m-%d', 'now') < strftime('%m-%d', birthdate)) AS age
FROM (SELECT *,date(substr(birthday,7,4) || '-' || substr(birthday,4,2) || '-' || substr(birthday,4,2)) AS birthdate
     from Staff) as b1 ; 
        
SELECT j.jobtitle_id,j.name,s.salary
from Staff s
JOIN Jobtitles j
ON j.jobtitle_id=s.jobtitle_id
WHERE s.salary<>(SELECT MAX(salary) from Staff)
ORDER by salary DESC
LIMIT 1

Результаты прогона по первой задаче:

Результаты прогона по второй задаче:

Результаты прогона по третьей задаче:

Теперь сохраняем это в g-доках и присылаем им ссылку на файл.)

Буквально минутное дело - решить блок с SQL в Tinkoff.

Table Of Contents

Предисловие

SQL часть

№1

№2

№3

№4

№5

№6

№7

№8

№9

№10

№11

Решение